﻿using BaseUtils.Logger;
using DatabaseCore.Modal;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

namespace DatabaseCore.Sqlite
{
    public class SqliteHelper : DataSourceClient
    {
        // 默认数据库索引 id 为 0；
        public static readonly int SystemDefaultDBID = 0;

        private SqlSugarClient db;
        public bool IsConnected;
        private static object DBLock = new object();

        public SqliteHelper()
        {
        }

        // 提供静态创建方法，确保连接被初始化
        public static SqliteHelper Create()
        {
            var helper = new SqliteHelper();
            helper.ConnectionSqlite(); // 强制执行连接
            return helper;
        }

        private readonly string SystemDefaultDB = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "projects", "App.db");

        // 连接字符串单独处理
        private string GetConnectionString(string dbPath)
        {
            return $"data source={dbPath};version=3;connect timeout=60;default command timeout=60;";
        }

        public SqliteHelper ConnectionSqlite(string connectionString = null)
        {
            if (db != null && IsConnected)
            {
                return this; // 已连接则直接返回
            }

            try
            {
                if (string.IsNullOrEmpty(connectionString))
                {
                    string dbDir = Path.GetDirectoryName(SystemDefaultDB);
                    if (!Directory.Exists(dbDir))
                        Directory.CreateDirectory(dbDir);
                    connectionString = GetConnectionString(SystemDefaultDB);
                }

                db = OpenConnection(connectionString, DbType.Sqlite);
                db.Ado.Open(); // 手动打开连接
                IsConnected = db.Ado.IsValidConnection();
                if (!IsConnected)
                {
                    LoggerService.Error("连接数据库失败：无法验证连接有效性");
                    db = null;
                }
                else
                {
                    db.Ado.Close(); // 验证后先关闭，使用时再打开
                }
            }
            catch (Exception ex)
            {
                IsConnected = false;
                db = null; // 连接失败则重置
                LoggerService.Error($"连接失败：{ex.Message}", ex);
            }
            return this;
        }

        // 所有数据库操作前检查 db 是否有效
        private void CheckDbValid()
        {
            if (db == null || !IsConnected)
            {
                throw new InvalidOperationException("数据库连接未初始化或已失效，请先调用ConnectionSqlite()");
            }
        }

        public SqliteHelper ConnectionMysql(string connectionString = null)
        {
            if (!string.IsNullOrEmpty(connectionString))
            {
                db = OpenConnection(connectionString, DbType.MySql);
                db.Ado.Open();
                IsConnected = db.Ado.IsValidConnection();
                db.Ado.Close();
            }
            return this;
        }

        /// <summary>
        /// 查找数据表表名
        /// </summary>
        public List<SystemTable> FindDatabaseTableNames()
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    string sql = "SELECT name FROM sqlite_master WHERE type='table';";
                    List<SystemTable> systemTables = db.Ado.SqlQuery<SystemTable>(sql);
                    List<SystemFieldMapping> mapping = Query<SystemFieldMapping>(s => true);

                    foreach (SystemTable item in systemTables)
                    {
                        item.TableAlias = item.name; // 默认别名
                        var tableMapping = mapping.FirstOrDefault(m => m.TableName == item.name);
                        if (tableMapping != null)
                        {
                            item.TableAlias = tableMapping.TableAlias;
                            item.TableName = item.name;
                        }
                    }
                    return systemTables;
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"查找数据表表名失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        /// <summary>
        /// 获取表结构和列别名
        /// </summary>
        public List<TableStructure> FindTableStructures(string tableName)
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    string sql = $"SELECT * FROM pragma_table_info(@tableName);";
                    List<TableStructure> tableStructures = db.Ado.SqlQuery<TableStructure>(sql,
                        new SugarParameter("@tableName", tableName));

                    List<SystemFieldMapping> mapping = Query<SystemFieldMapping>(m => m.TableName == tableName);

                    foreach (TableStructure item in tableStructures)
                    {
                        item.Column = item.name;
                        item.Alias = item.name;

                        var fieldMapping = mapping.FirstOrDefault(m => m.FieldName == item.name);
                        if (fieldMapping != null)
                        {
                            item.Alias = fieldMapping.FieldAlias;
                            item.MappingId = fieldMapping.Id;
                        }
                    }
                    return tableStructures;
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"获取表结构失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        /// <summary>
        /// 根据sql语句查询
        /// </summary>
        public DBResult QueryBySql(string sql)
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    List<object> resultData = db.Ado.SqlQuery<object>(sql);
                    DBResult result = new DBResult(resultData);
                    result.TotalCount = resultData.Count;
                    return result;
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"SQL查询失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        public DBResult PageQueryBySql(string Sql, string tableName, int Page, int Size)
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                                   // string paginationSql = $"{sql} LIMIT {size} OFFSET {(page - 1) * size}";
                    DBResult Result = QueryBySql(Sql);
                    string[] _Sql = Regex.Split(Sql, "limit");
                    string CountSql = _Sql[0].Replace("SELECT * FROM", "SELECT Count(*) FROM ") + ";";

                    int Count = db.Ado.SqlQuery<int>(CountSql).FirstOrDefault();
                    return new DBResult(Result.Data, Count, Page, Size);
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"分页查询失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        /// <summary>
        /// 插入、更新和删除
        /// 返回：影响的行数
        /// </summary>
        public int ExecuteSql(string sql)
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Ado.ExecuteCommand(sql);
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"执行SQL失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        public List<T> QueryAll<T>() where T : class, new()
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Queryable<T>().ToList();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"查询所有数据失败：{ex.Message}", ex);
                    return new List<T>();
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        public List<T> Query<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Queryable<T>().Where(whereExpression).ToList();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"条件查询失败：{ex.Message}", ex);
                    return new List<T>();
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        public T QueryOne<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Queryable<T>().Where(whereExpression).First();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"查询单条数据失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        public bool Insert<T>(T entity) where T : class, new()
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Insertable(entity).ExecuteCommand() > 0;
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"插入数据失败：{ex.Message}", ex);
                    return false;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }
      
        /// <summary>
        /// 删除数据
        /// </summary>
        public bool Delete<T>(T entity, Expression<Func<T, bool>> WhereCondition = null) where T : class, new()
        {
            bool res = false;
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
               
                    if (WhereCondition != null)
                    {
                        res = db.DeleteableWithAttr(entity).EnableQueryFilter().Where(WhereCondition).ExecuteCommand() > 0;
                    }
                    else
                    {
                        res = db.Deleteable(entity).ExecuteCommand() > 0;
                    }
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"删除数据失败：{ex.Message}", ex);
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }

                return res;
            }
        }
        public bool Delete<T>( Expression<Func<T, bool>> WhereCondition) where T : class, new()
        {
            bool res = false;
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接

                    res = db.Deleteable<T>().EnableQueryFilter().Where(WhereCondition).ExecuteCommand() > 0;
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"删除数据失败：{ex.Message}", ex);
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }

                return res;
            }
        }

        public string Query(string pt, string stationName, string materialName)
        {
            throw new NotImplementedException();
        }

        public T Install<T>(T t) where T : class, new()
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Insertable(t).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnEntity();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"安装数据失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        int DataSourceClient.Update<T>(T t)
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"更新单条数据失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        int DataSourceClient.Update<T>(List<T> t)
        {
            lock (DBLock)
            {
                CheckDbValid();
                try
                {
                    db.Ado.Open(); // 手动打开连接
                    return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"更新多条数据失败：{ex.Message}", ex);
                    throw;
                }
                finally
                {
                    if (db != null && db.Ado.IsValidConnection())
                        db.Ado.Close(); // 手动关闭连接
                }
            }
        }

        public SqlSugarClient OpenConnection(string connectionString, DbType type)
        {
            var ssc = new SqlSugarClient(new ConnectionConfig()
            {
                DbType = type,
                ConnectionString = connectionString,
                IsAutoCloseConnection = false, // 禁用自动关闭连接
                InitKeyType = InitKeyType.Attribute // 明确主键配置
            });

            // 调试 打印SQL
            ssc.Aop.OnLogExecuting = (sql, pars) =>
            {
                LoggerService.Info($"{sql}\r\n" +
                    $"{ssc.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))}");
            };

            return ssc;
        }

        /// <summary>
        /// 手动关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            if (db != null && db.Ado.IsValidConnection())
            {
                try
                {
                    db.Ado.Close();
                    IsConnected = false;
                }
                catch (Exception ex)
                {
                    LoggerService.Error($"关闭数据库连接失败：{ex.Message}", ex);
                }
            }
        }

     

      
    }
}
